﻿Imports System.Globalization
Imports Microsoft.Office.Interop.Owc11

Partial Class WebForm_ManageFinanceYTDReport_FinanceYTDReport
    Inherits System.Web.UI.UserControl

    Protected Sub ProductHierarchyControl1_Submit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Search1.Submit
        LoadData()
    End Sub

    Protected Sub ProductHierarchyControl1_Export(ByVal sender As Object, ByVal e As System.EventArgs) Handles Search1.Export
        ExportData()
    End Sub

    Protected Sub LoadData()
        pl.Visible = True

        Dim v As MyVelocity = InitMain()

        MyObj.xlsData = v.Output()

        pl.DataBind()
    End Sub

    Protected Sub ExportData()
        pl.Visible = True

        Dim v As MyVelocity = InitMain()

        v.Put("Export", "true")


        'Xml轉Xlsx
        'Dim MyExport As New clsExport
        'MyExport.XmlToXlsx("YTD.xlsx", String.Format("TCReportExportGroupBy{0}(" & TCModel.Tc_SignatureText & ")", ExportBy))
        'MyObj.xlsData = v.Output()
        'MyObj.WriteLog(v.Output)

        Dim MyRandom As New clsRandom
        Dim FileName As String = Me.MapPath("~\tmp\") & MyRandom.Generate(8)

        Dim sr As New IO.StreamWriter(FileName, True)
        sr.WriteLine(v.Output)
        sr.Close()

        'Xml轉Xlsx
        Dim MyExport As New clsExport
        MyExport.XmlToXlsx(FileName, String.Format("YTDReport"))

        pl.DataBind()
    End Sub

    Protected Function InitMain() As MyVelocity
        Dim v As New MyVelocity("YTD-Main.xml")

        v.Put("S1", "##")
        v.Put("Model", Me)
        v.Put("EndMonth", New DateTime(Search1.Year, Search1.EndMonth, 1))
        v.Put("SheetName", "Summary")
        v.Put("SignatureText", Search1.SignatureText)
        v.Put("ChannelText", Search1.ChannelText)
        v.Put("Hier1Text", Search1.Hier1Text)
        v.Put("ColumnCount", 14 + Search1.SigList.Count * 2)

        currCH = Search1.Channel
        currHier1 = Search1.Hier1

        Return v
    End Function

    Public Function ExportByCG() As String
        Dim buffer As New StringBuilder
        Dim dat As New clsData

        For Each ch As String In Search1.ChList
            Dim sql As New StringBuilder
            Dim paras As New List(Of SqlParameter)

            If Search1.Channel <> MyObj.GetDDLSelectAllItemV2.Text And Search1.Hier1 <> MyObj.GetDDLSelectAllItemV2.Text Then
                sql.AppendFormat("Select DISTINCT C_Hier_1, C_Hier_1Name from channelhier1view ")
                sql.AppendFormat("WHERE Channel = @Channel AND c_hier_1 = @c_hier_1 ")

                paras.Add(New SqlParameter("Channel", ch))
                paras.Add(New SqlParameter("c_hier_1", Search1.Hier1))
            Else
                sql.AppendFormat("Select DISTINCT C_Hier_1, C_Hier_1Name from channelhier1view ")
                sql.AppendFormat("WHERE Channel = @Channel ")

                paras.Add(New SqlParameter("Channel", ch))
            End If

            For Each dr As DataRow In dat.GetData(sql.ToString, paras.ToArray).Rows
                Dim v As New MyVelocity("YTD.xml")

                v.Put("Model", Me)
                v.Put("SheetName", MyObj.GenValidSheetName(String.Format("{0}-{1}", GetChannelData.Rows.Find(ch)("ChannelName"), dr("C_Hier_1Name"))))
                v.Put("EndMonth", New DateTime(Search1.Year, Search1.EndMonth, 1))
                v.Put("SignatureText", Search1.SignatureText)
                v.Put("ChannelText", GetChannelData.Rows.Find(ch)("ChannelName"))
                v.Put("Hier1Text", dr("C_Hier_1Name"))
                v.Put("ColumnCount", 14 + Search1.SigList.Count * 2)

                currCH = ch
                currHier1 = dr("C_Hier_1")

                buffer.AppendFormat(v.Output)
            Next
        Next

        Return buffer.ToString
    End Function

    ' Sig, channel, hier1 data
    Public Function GetSigData() As DataTable
        Dim key = "_GetSigData"

        If (HttpContext.Current.Items(key) Is Nothing) Then
            Dim dat As New clsData
            Dim sql As New StringBuilder
            Dim paras As New List(Of SqlParameter)

            sql.AppendFormat("select code, description from dbo.ProductHierarchyOriginal ")
            sql.AppendFormat("WHERE LevelName = 'Signature' AND code IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Search1.SigList))

            Dim dt As DataTable = dat.GetData(sql.ToString, paras.ToArray)

            dt.PrimaryKey = New DataColumn() {dt.Columns("code")}

            HttpContext.Current.Items(key) = dt
        End If

        Return TryCast(HttpContext.Current.Items(key), DataTable)
    End Function

    Public Function GetChannelData() As DataTable
        Dim key = "_GetChannelData"

        If (HttpContext.Current.Items(key) Is Nothing) Then
            Dim dat As New clsData
            Dim sql As New StringBuilder
            Dim paras As New List(Of SqlParameter)

            sql.AppendFormat("Select DISTINCT Channel, ChannelName from channelhier1view ")
            sql.AppendFormat("WHERE Channel IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Search1.ChList))

            Dim dt As DataTable = dat.GetData(sql.ToString, paras.ToArray)

            dt.PrimaryKey = New DataColumn() {dt.Columns("Channel")}

            HttpContext.Current.Items(key) = dt
        End If

        Return TryCast(HttpContext.Current.Items(key), DataTable)
    End Function

    Public Function GetHier1Data() As DataTable
        Dim key = "_GetHier1Data"

        If (HttpContext.Current.Items(key) Is Nothing) Then
            Dim dat As New clsData
            Dim sql As New StringBuilder
            Dim paras As New List(Of SqlParameter)

            sql.AppendFormat("Select DISTINCT C_Hier_1, C_Hier_1Name from channelhier1view ")
            sql.AppendFormat("WHERE C_Hier_1 IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Search1.Hier1List))

            Dim dt As DataTable = dat.GetData(sql.ToString, paras.ToArray)

            dt.PrimaryKey = New DataColumn() {dt.Columns("C_Hier_1")}

            HttpContext.Current.Items(key) = dt
        End If

        Return TryCast(HttpContext.Current.Items(key), DataTable)
    End Function

    ' YTD Data
    Public Function GetItemData(ByVal year As Integer, ByVal itemName As String) As Long
        Dim temp As Long

        For Each sig As String In Search1.SigList
            temp += GetItemData(year, itemName, sig)
        Next

        Return temp
    End Function

    Protected currCH As String, currHier1 As String

    Public Function GetItemData(ByVal year As Integer, ByVal itemName As String, ByVal sig As String) As Long
        Dim result() As DataRow = GetData(currCH, currHier1).Select(String.Format("YYMM = '{0}' AND ItemName='{1}' AND Signature='{2}'", year, itemName, sig))
        Dim temp As Long

        If result.Length = 0 Then
            temp = 0
        Else
            If result(0)("value") IsNot DBNull.Value Then
                temp = Convert.ToInt64(result(0)("value"))
            End If            
        End If

        Return temp
    End Function

    Public Function GetData() As DataTable
        Return GetData(Search1.Channel, Search1.Hier1)
    End Function

    Public Function GetData(ByVal ch As String, ByVal hier1 As String) As DataTable
        Dim key As String = "_GetData" + ch + hier1

        If (HttpContext.Current.Items(key) Is Nothing) Then
            Dim dat As New clsData
            Dim sql As New StringBuilder
            Dim paras As New List(Of SqlParameter)

            Dim mons As New List(Of Integer)

            For i As Integer = 1 To Search1.EndMonth
                mons.Add(i)
            Next

            Dim years As New List(Of Integer)

            years.Add(Search1.Year)
            years.Add(Search1.Year - 1)

            sql.AppendFormat("SELECT YYMM, Signature, ItemName, SUM({0}) AS value FROM TCReportAHier1Data ", MyObj.JoinFormat("+", "Mon{0}", mons))
            sql.AppendFormat("WHERE signature IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Search1.SigList))
            sql.AppendFormat("AND YYMM IN ({0}) ", MyObj.JoinFormat(",", "{0}", years))

            If ch <> MyObj.GetDDLSelectAllItemV2.Text Then
                paras.Add(New SqlParameter("Channel", ch))
                sql.AppendFormat("AND Channel = @Channel ")
            End If

            If hier1 <> MyObj.GetDDLSelectAllItemV2.Text Then
                paras.Add(New SqlParameter("Hier1", hier1))
                sql.AppendFormat("AND Hier1 = @Hier1 ")
            End If

            sql.AppendFormat("GROUP BY YYMM, Signature, ItemName ")

            HttpContext.Current.Items(key) = dat.GetData(sql.ToString, paras.ToArray)            
        End If

        Return TryCast(HttpContext.Current.Items(key), DataTable)
    End Function

    ' Evol
    Public Function GetEvol(ByVal year As Integer, ByVal itemName As String) As Single
        Dim curr As Long = GetSaveItemData(year, itemName)
        Dim prev As Long = GetSaveItemData(year - 1, itemName)

        If prev = 0 Then
            Return 0
        Else
            Return (curr / prev) - 1
        End If
    End Function

    ' YTD Save Data
    Public Function GetSaveItemData(ByVal year As Integer, ByVal itemName As String) As Long
        Dim temp As Long

        For Each sig As String In Search1.SigList
            temp += GetSaveItemData(year, itemName, sig)
        Next

        Return temp
    End Function

    Public Function GetSaveItemData(ByVal year As Integer, ByVal itemName As String, ByVal sig As String) As Long
        Dim result() As DataRow = GetSaveData(currCH, currHier1).Select(String.Format("YYMM = '{0}' AND ItemName='{1}' AND Signature='{2}'", year, itemName, sig))
        Dim temp As Long

        If result.Length = 0 Then
            temp = 0
        Else
            If result(0)("value") IsNot DBNull.Value Then
                temp = Convert.ToInt64(result(0)("value"))
            End If
        End If

        Return temp
    End Function

    Public Function GetSaveData() As DataTable
        Return GetData(Search1.Channel, Search1.Hier1)
    End Function

    Public Function GetSaveData(ByVal ch As String, ByVal hier1 As String) As DataTable
        Dim key = "_GetSaveData" + ch + hier1

        If (HttpContext.Current.Items(key) Is Nothing) Then
            Dim dat As New clsData
            Dim sql As New StringBuilder
            Dim paras As New List(Of SqlParameter)

            Dim years As New List(Of Integer)

            years.Add(Search1.Year)
            years.Add(Search1.Year - 1)

            sql.AppendFormat("SELECT YYMM, Signature, ItemName, SUM({0}) AS value FROM TCReportYTDACT ", "YTDValue")
            sql.AppendFormat("WHERE signature IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Search1.SigList))
            sql.AppendFormat("AND YYMM IN ({0}) ", MyObj.JoinFormat(",", "{0}", years))

            If ch <> MyObj.GetDDLSelectAllItemV2.Text Then
                paras.Add(New SqlParameter("Channel", ch))
                sql.AppendFormat("AND Channel = @Channel ")
            End If

            If hier1 <> MyObj.GetDDLSelectAllItemV2.Text Then
                paras.Add(New SqlParameter("Hier1", hier1))
                sql.AppendFormat("AND Hier1 = @Hier1 ")
            End If

            sql.AppendFormat("GROUP BY YYMM, Signature, ItemName ")

            HttpContext.Current.Items(key) = dat.GetData(sql.ToString, paras.ToArray)
        End If

        Return TryCast(HttpContext.Current.Items(key), DataTable)
    End Function
    '

    Public Function ToAbevMonth(ByVal month As Integer) As String
        Dim c As New CultureInfo("en-us")

        Return c.DateTimeFormat.AbbreviatedMonthNames()(month - 1).ToUpper()
    End Function

    Private ReadOnly Property spreadsheet() As Spreadsheet
        Get
            If HttpContext.Current.Items("SpreadSheet") Is Nothing Then
                'prepare owc data
                Dim xmlData As String = HttpUtility.HtmlDecode(Spreadsheet1Data.Value)

                Dim _spreadsheet As New Spreadsheet

                _spreadsheet.XMLData = xmlData
                Spreadsheet1Data.Value = ""

                HttpContext.Current.Items("SpreadSheet") = _spreadsheet
            End If

            Return TryCast(HttpContext.Current.Items("SpreadSheet"), Spreadsheet)
        End Get
    End Property

    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If SaveData() = True Then
            Me.LoadData()
            MyObj.ShowMessage(Me.GetLocalResourceObject("btnSave_Success"))
        End If
        'MyObj.WriteLog(Spreadsheet1Data.Value)
    End Sub

    Protected Function SaveData() As Boolean
        Dim dat As New clsData()
        Dim sql As New StringBuilder
        Dim paras As New List(Of SqlParameter)

        'start row
        Dim startRow As Integer = Me.spreadsheet.Range("B:B").Find("UNITS  Before Returns").Row

        'write to database

        Dim currentRow As Integer = startRow

        'For i As Integer = currentRow To currentRow + 20
        '    MyObj.WriteLog(Me.spreadsheet.Cells(i, 2).Value)
        '    MyObj.WriteLog(Me.spreadsheet.Cells(i, 5).Interior.Color)
        'Next
        'Return True

        'clear previous data
        sql.Length = 0
        paras.Clear()

        sql.AppendFormat("DELETE FROM TCReportYTDACT WHERE YYMM = @YYMM AND Signature = @Signature AND Channel = @Channel AND Hier1 = @Hier1 ")

        paras.Add(New SqlParameter("YYMM", Search1.Year))
        paras.Add(New SqlParameter("Signature", Search1.Signature))
        paras.Add(New SqlParameter("Channel", Search1.Channel))
        paras.Add(New SqlParameter("Hier1", Search1.Hier1))

        dat.AccData(sql.ToString, paras.ToArray)
        '

        While SaveData_IsEnd(currentRow) = False

            If SaveData_IsInputRow(currentRow) = False Then
                currentRow += 1
                Continue While
            End If            

            Dim itemName As String = Me.spreadsheet.Cells(currentRow, 2).Value
            Dim value As String = Me.spreadsheet.Cells(currentRow, 5).Value

            sql.Length = 0
            paras.Clear()

            sql.AppendFormat("INSERT TCReportYTDACT (YYMM,Signature,Channel,Hier1,ItemName,YTDValue) ")
            sql.AppendFormat("SELECT @YYMM,@Signature,@Channel,@Hier1,@ItemName,@YTDValue ")

            paras.Add(New SqlParameter("YYMM", Search1.Year))
            paras.Add(New SqlParameter("Signature", Search1.Signature))
            paras.Add(New SqlParameter("Channel", Search1.Channel))
            paras.Add(New SqlParameter("Hier1", Search1.Hier1))
            paras.Add(New SqlParameter("ItemName", itemName))
            paras.Add(New SqlParameter("YTDValue", value))

            dat.AccData(sql.ToString, paras.ToArray)

            currentRow += 1
        End While

        Return True
    End Function

    Protected Function SaveData_IsInputRow(ByVal row As Integer) As Boolean
        Dim r As Range = Me.spreadsheet.Cells(row, 5)

        If r.Interior.Color.ToString = "10092543" Then
            Return True
        Else
            Return False
        End If
    End Function

    Protected Function SaveData_IsEnd(ByVal row As Integer) As Boolean
        Dim r As Range = Me.spreadsheet.Cells(row, 2)

        Return r.Text = "PRODUCT MARGIN" Or r.Value.ToString.Trim.Length = 0
    End Function
End Class
